﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using MySQLStudio.DataAccess;
using MySQLStudio.Common;

namespace MySQLStudio.BusinessLogic
{
    public class RoutinesController
    {
        /// <summary>
        /// Retrieves list of stored procedures in database
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public static List<StoredProcedure> GetStoredProcedures(string connectionString, string databaseName)
        {
            IDataReader reader = null;
            List<StoredProcedure> lstStoredProcedures = new List<StoredProcedure>();

            try
            {
                reader = MySqlDataAccess.ExecuteDataReader(connectionString, InformationSchemaHelper.GetStoredProceduresDatabaseCommand(databaseName));

                lstStoredProcedures = new List<StoredProcedure>();

                while (reader.Read())
                {
                    lstStoredProcedures.Add(new StoredProcedure(Convert.ToString(reader["routine_name"]),
                                                               Convert.ToString(reader["routine_schema"]),
                                                               Convert.ToString(reader["routine_type"]),
                                                               Convert.ToDateTime(reader["created"]),
                                                               DataUtils.GetNullableDateTime(reader, "last_altered"),
                                                               Convert.ToString(reader["definer"])
                                                              ));
                }

            }
            catch (Exception ex)
            {
                throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_extract_stored_procedures_in_database, databaseName), ex);
            }
            finally
            {
                if (reader != null)
                {
                    if (!reader.IsClosed)
                        reader.Close();
                }
            }

            return lstStoredProcedures;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="createProcedureSQL"></param>
        public static void CreateStoredProcedure(string connectionString, string createProcedureSQL)
        {
            try
            {
                int errCode = MySqlDataAccess.ExecuteNonQuery(connectionString, createProcedureSQL);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_execute_create_procedure_statement, createProcedureSQL), ex);
            }
        }
    }
}
